using AdminApiTemplate.Cache;
using AdminApiTemplate.Common.Exceptions;
using AdminApiTemplate.Common.Helpers;
using AdminApiTemplate.Common.Models;

namespace AdminApiTemplate.Api.Helpers
{
    /// <summary>
    /// 数据相关操作
    /// </summary>
    public class DataHelper
    {
        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <typeparam name="Tin"></typeparam>
        /// <typeparam name="Tout"></typeparam>
        /// <param name="request"></param>
        /// <param name="tables"></param>
        /// <param name="fields"></param>
        /// <param name="orderByFieldsAll"></param>
        /// <param name="sps"></param>
        /// <returns></returns>
        public static PagedList<Tout> GetPagedList<Tin, Tout>(Tin request, string tables, string fields, string orderByFieldsAll, params SqlParameterItem[] sps) where Tin : PagedRequest
        {
            var dict = new Dictionary<string, object>();
            var filter = ParameterHelper.BuilderFilter<Tin>(request, dict, sps);

            string sql;
            var items = new List<Tout>();
            var totalCount = 0L;
            if (!request.Export.GetValueOrDefault())
            {
                if (string.IsNullOrEmpty(orderByFieldsAll))
                {
                    throw new WithCodeException((int)ErrorCodeEnum.CodeError, "排序字段必填", "orderByFieldsAll必填");
                }

                var orderByFields = orderByFieldsAll.Split(',');
                var orderByField = orderByFields[0];
                if (!string.IsNullOrEmpty(request.OrderByField))
                {
                    if (orderByFields.Any(a => a.ToLower().Contains(request.OrderByField.ToLower())))
                    {
                        orderByField = request.OrderByField;
                    }
                    else
                    {
                        throw new WithCodeException((int)ErrorCodeEnum.InvalidArguments, "无效的排序：" + request.OrderByField);
                    }
                }

                var orderBy = "desc";
                if (!request.OrderByDesc.GetValueOrDefault(true))
                {
                    orderBy = "";
                }

                var limit = "";
                if (request.PageSize.GetValueOrDefault()>0)
                {
                    var skip = request.PageSize.GetValueOrDefault(20) * (request.Page.GetValueOrDefault(1) - 1);
                    var take = request.PageSize.GetValueOrDefault(20);
                    limit = $"limit {skip},{take}";
                }

                if (!request.OnlyItems.GetValueOrDefault())
                {
                    sql = $"select count(1) from {tables} {filter}";
                    totalCount = CacheHelper.DefaultQuery.ExecuteScalar<long>(sql, dict);
                }

                if (!request.OnlyTotalCount.GetValueOrDefault())
                {
                    sql = $"select {fields} from {tables} {filter} order by {orderByField} {orderBy} {limit}";
                    items = CacheHelper.DefaultQuery.GetList<Tout>(sql, dict);
                }
            }
            else
            {
                sql = $"select {fields} from {tables} {filter}";
                items = CacheHelper.DefaultQuery.GetList<Tout>(sql, dict);
            }

            return new PagedList<Tout>
            {
                Items = items,
                IndexFrom = 1,
                PageIndex = request.Page.GetValueOrDefault(1),
                PageSize = request.PageSize.GetValueOrDefault(20),
                TotalCount = totalCount
            };
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="request"></param>
        /// <param name="tables"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static string Add<T>(T request, string tables, string fields)
        {
            var parts = fields.Split(',').Select(o => o.Replace("`", "")).ToList();
            if (parts.Any(a => string.IsNullOrEmpty(a)))
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "新增失败，部分字段无效", "fields无效，不能有空字段");
            }

            var fields_real = string.Join(",", parts.Select(o => $"`{o}`"));
            var pairs = string.Join(",", parts.Select(o => $"@{o}"));
            var sql = $"insert into {tables}({fields_real}) values({pairs})";
            CacheHelper.DefaultQuery.Execute(sql, request);
            return "新增成功";
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="request"></param>
        /// <param name="tables"></param>
        /// <param name="fields"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static string Add<T>(T request, string tables, string fields, ref long id)
        {
            var parts = fields.Split(',').Select(o => o.Replace("`", "")).ToList();
            if (parts.Any(a => string.IsNullOrEmpty(a)))
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "新增失败，部分字段无效", "fields无效，不能有空字段");
            }

            var fields_real = string.Join(",", parts.Select(o => $"`{o}`"));
            var pairs = string.Join(",", parts.Select(o => $"@{o}"));
            var sql = $"insert into {tables}({fields_real}) values({pairs});select @@IDENTITY;";
            id = CacheHelper.DefaultQuery.ExecuteScalar<long>(sql, request);
            return "新增成功";
        }

        /// <summary>
        /// 获取
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="request"></param>
        /// <param name="tables"></param>
        /// <param name="fields"></param>
        /// <param name="sps"></param>
        /// <returns></returns>
        public static T GetOne<T>(T request, string tables, string fields, params SqlParameterItem[] sps)
        {
            var parts = fields.Split(',').Select(o => o.Replace("`", "")).ToList();
            if (parts.Any(a => string.IsNullOrEmpty(a)))
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "获取失败，部分字段无效", "fields无效，不能有空字段");
            }

            var dict = ConvertHelper.ToDictionary(request);
            for (var i = parts.Count-1; i>=0; i--)
            {
                if (!dict.ContainsKey(parts[i]))
                {
                    parts.RemoveAt(i);
                }
            }

            if (parts.Count == 0)
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "获取失败，没有要获取的字段");
            }

            var filter = ParameterHelper.BuilderFilter<T>(request, dict, sps);
            if (string.IsNullOrEmpty(filter))
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "获取失败，不能执行没有条件的获取操作");
            }

            var fields_real = string.Join(",", parts.Select(o => $"`{o}`"));
            var sql = $"select {fields_real} from {tables} {filter} limit 1";
            var items = CacheHelper.DefaultQuery.GetList<T>(sql, dict);
            return items.FirstOrDefault();
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="request"></param>
        /// <param name="tables"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static string Edit<T>(T request, string tables, string fields) where T : EditRequest
        {
            var parts = fields.Split(',').Select(o => o.Replace("`", "")).ToList();
            if (parts.Any(a => string.IsNullOrEmpty(a)))
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "修改失败，部分字段无效", "fields无效，不能有空字段");
            }

            var dict = ConvertHelper.ToDictionary(request, true);
            if (parts.Count == 0)
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "修改失败，没有要修改的字段");
            }

            var filter = ParameterHelper.BuilderFilter<T>(request, dict);
            if (string.IsNullOrEmpty(filter))
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "修改失败，不能执行没有条件的修改操作");
            }

            var pairs = string.Join(",", parts.Select(o => $"`{o}`=@{o}"));
            var sql = $"update {tables} set {pairs} {filter}";
            CacheHelper.DefaultQuery.Execute(sql, dict);
            return "修改成功";
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="request"></param>
        /// <param name="tables"></param>
        /// <returns></returns>
        public static string Del<T>(T request, string tables) where T : EditRequest
        {
            var dict = new Dictionary<string, object>();
            var filter = ParameterHelper.BuilderFilter<T>(request, dict);
            if (string.IsNullOrEmpty(filter))
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "删除失败，不能执行没有条件的删除操作");
            }
            var sql = $"delete from {tables} {filter}";
            CacheHelper.DefaultQuery.Execute(sql, dict);
            return "删除成功";
        }
    }
}
